package com.sbl.bajw.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;

import com.sbl.bajw.common.web.WebContext;
import com.sbl.bajw.util.LogUtil;

public class DbManager {
	
	static Connection conn;
	
	static Logger log;
	
	static ThreadLocal<Connection> threadConn = new ThreadLocal<Connection>();
	
	static{
		log = LogUtil.getLogger("dbmanager");
	}
	
	public static Connection getConn() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
		conn = threadConn.get();
		
		if(conn == null || conn.isClosed()){
			Class.forName("oracle.jdbc.OracleDriver").newInstance(); //MYSQL驱动
			String DBConnectionString = WebContext.getWebInitParameter("DBConnectionString");
			String DBAccount = WebContext.getWebInitParameter("DBAccount");
			String DBPassword = WebContext.getWebInitParameter("DBPassword");
			log.info(DBConnectionString+","+DBAccount+","+DBPassword);
			conn = DriverManager.getConnection(DBConnectionString, DBAccount, DBPassword);
			threadConn.set(conn);
		}
		
		return conn;
	}
	
	public static DataTable executeQuery(String sql,List<String> parm) throws Exception{
		conn = getConn();
		try {
			PreparedStatement pr = conn.prepareStatement(sql);
			if(parm != null){
				for(int i =0;i<parm.size();i++){
					String value = parm.get(i);
					if(value != null){
						pr.setString(i+1,value);
					}
				}
			}
			
			ResultSet resultSet = pr.executeQuery();
			DataTable table = new DataTable(resultSet);
			pr.close();
			return table;
		} catch (Exception e) {
			System.out.println(sql+",参数"+parm);
			rollback();
			throw e;
		}
	}
	
	public static void executeUpdate(String sql,List<String> parm) throws Exception{
		conn = getConn();
		try {
			PreparedStatement pr = conn.prepareStatement(sql);
			if(parm != null){
				for(int i =0;i<parm.size();i++){
					String value = parm.get(i);
					if(value != null){
						pr.setString(i+1,value);
					}
				}
			}
			
			pr.executeUpdate();
			pr.close();
		} catch (Exception e) {
			System.out.println(sql+",参数"+parm);
			rollback();
			throw e;
		}
	}
	
	public static Object executeScalar(String sql,List<String> parm) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
		conn = getConn();
		PreparedStatement pr = conn.prepareStatement(sql);
		Object resultVal = null;
		
		if(parm != null){
			for(int i =0;i<parm.size();i++){
				String value = parm.get(i);
				if(value != null){
					pr.setString(i+1,value);
				}
			}
		}
		
		ResultSet result = pr.executeQuery();
		
		if(result.next())
		{
			resultVal = result.getObject(1);
		}
		pr.close();
		return resultVal;
	}
	
	public static void commit() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
		conn = getConn();
		conn.commit();
		conn.close();
	}
	
	public static void beginTransaction() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
		conn = getConn();
		conn.setAutoCommit(false);
	}

	public static void rollback() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
		Connection conn =  getConn();
		conn.setAutoCommit(true);
		conn.rollback();
		conn.close();
	}
}
